﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Web.Library.DomainObject.SQLServer;
using System.Data;
using Dapper;

namespace Web.Library.DataAccess.SQLServer
{
    public partial class SysUserRepo
    {
        public bool ValidateUser(string UserCode, string Password, out SysUser t)
        {
            bool result = false;
            t = null;
            using (IDbConnection conn = base.GetConnection(connectionString))
            {
                string strQuery = "SELECT Name,UserID, UserCode, Password, GroupID, GroupAppID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy FROM dbo.SysUser with(nolock) WHERE Lower(UserCode) = Lower(@UserCode) AND Password=@Password";
                try
                {
                    conn.Open();
                    t = conn.Query<SysUser>(strQuery, new { UserCode = UserCode,Password = Password }).SingleOrDefault();
                   
                    result = t==null ? false :  true;
                }
                catch (Exception ex)
                {
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }

            }
            return result;
        }

        public List<SysUser> GetListMore(int pageSize, int currentPage, string sortName, string sortOrder)
        {
            List<SysUser> tt = new List<SysUser>();
            using (IDbConnection conn = base.GetConnection(connectionString))
            {
                int startRow = ((currentPage - 1) * pageSize) + 1;
                int endRow = (startRow + pageSize);

                StringBuilder sbQuery = new StringBuilder();
                sbQuery.AppendFormat(" WITH result_set AS ");
                sbQuery.AppendFormat(" ( ");
                sbQuery.AppendFormat("    SELECT ");
                sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                sbQuery.AppendFormat("      SysUser.UserID, SysUser.UserCode, SysUser.Password, SysUser.GroupID, SysUser.GroupAppID, SysUser.CreatedDate, SysUser.CreatedBy, SysUser.UpdatedDate, SysUser.UpdatedBy ");
                sbQuery.AppendFormat("    FROM ");
                sbQuery.AppendFormat("      dbo.SysUser with(nolock) INNER JOIN SysGroups ON SysUser.GroupID = SysGroups.GroupID  ");
                sbQuery.AppendFormat(" ) ");
                sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                try
                {
                    conn.Open();
                    tt = conn.Query<SysUser, SysGroups, SysUser>(sbQuery.ToString(), (user, group) => { user.GroupID = group.GroupID; return user; }, splitOn: "GroupID").ToList<SysUser>();

                    //conn.Query(sbQuery.ToString(), (a, s) => {s. });                    //conn.QueryMultiple(sbQuery.ToString(),
                }
                catch (Exception ex)
                {
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }

            return tt;
        }

        public SysGroups LoadGroups(int key)
        {
            SysGroups t = null;
            using (IDbConnection conn = base.GetConnection(connectionString))
            {
                string strQuery = "SELECT GroupID, GroupName, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy FROM dbo.SysGroups with(nolock) WHERE GroupID = @GroupID";
                try
                {
                    conn.Open();
                    t = conn.Query<SysGroups>(strQuery, new { GroupID = key }).SingleOrDefault();
                }
                catch (Exception ex)
                {
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }

            }
            return t;
        }

        public SysApplicationGroups LoadGroupApp(int key)
        {
            SysApplicationGroups t = null;
            using (IDbConnection conn = base.GetConnection(connectionString))
            {
                string strQuery = "SELECT AppGroupID, AppGroupName, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy FROM dbo.SysApplicationGroups with(nolock) WHERE AppGroupID = @AppGroupID";
                try
                {
                    conn.Open();
                    t = conn.Query<SysApplicationGroups>(strQuery, new { AppGroupID = key }).SingleOrDefault();
                }
                catch (Exception ex)
                {
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }

            }
            return t;
        }

        public bool UpdateProfile(SysUser domain)
        {
            int result = 0;
            using (IDbConnection conn = base.GetConnection(connectionString))
            {
                string sqlQuery = "UPDATE dbo.SysUser SET Name = @Name, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE UserID = @UserID";

                try
                {
                    conn.Open();
                    result = conn.Execute(sqlQuery, domain);
                }
                catch (Exception ex)
                {
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return (result > 0);
        }

        public bool UpdateResetPassword(SysUser domain)
        {
            int result = 0;
            using (IDbConnection conn = base.GetConnection(connectionString))
            {
                string sqlQuery = "UPDATE dbo.SysUser SET Password = @Password, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE UserID = @UserID";

                try
                {
                    conn.Open();
                    result = conn.Execute(sqlQuery, domain);
                }
                catch (Exception ex)
                {
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return (result > 0);
        }
    }
}
